package cn.edu.klmy.dao.impl;

import cn.edu.klmy.dao.AbstractJdbcTemplate;
import cn.edu.klmy.dao.IStudyDao;
import cn.edu.klmy.models.student.Student;
import cn.edu.klmy.models.study.CourseStudentLogs;
import cn.edu.klmy.models.study.CourseStudentsLog;
import cn.edu.klmy.models.study.TeacherCourseElective;
import cn.edu.klmy.models.studylog.StudyLogSituation;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @author 顾伟
 *         创建日期：13-5-28
 */
@Repository
public class StudyDao extends AbstractJdbcTemplate implements IStudyDao {

    public List<Student> getNoElectionStudentsByConditions(int term, int section, int time, int week,String grade) {
        String scheduleQuery = "SELECT COUNT(*) FROM COURSE_SCHEDULING " +
                "WHERE RESTRICTION_TERM&" + term + "=" + term +
                " AND RESTRICTION_WEEK&" + week + "=" + week +
                " AND RESTRICTION_TIME&" + time + "=" + time +
                " AND RESTRICTION_SECTION&" + section + "=" + section;
        int scheduleCount=this.jdbcTemplate.queryForInt(scheduleQuery);
        if(scheduleCount!=0){
            String sql="SELECT B.* FROM STUDENT B " +
                    "WHERE B.ID NOT IN (SELECT DISTINCT A.STUDENT_ID "  +
                    "FROM STUDENT_COURSE_ELECTIVE A "  +
                    "WHERE A.ELECTIVE_TERM=" + term  +
                    " AND A.ELECTIVE_SECTION=" + section +
                    " AND A.ELECTIVE_TIME=" + time  +
                    " AND A.ELECTIVE_WEEK=" + week +
                    " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "')" +
                    " AND (B.STUDY_CLASS_ID IN (SELECT ID FROM CLASS WHERE CLASS_YEAR='"+grade+"')" +
                    " OR B.ADMIN_CLASS_ID IN (SELECT ID FROM CLASS WHERE CLASS_YEAR='"+grade+"'))";
            return this.jdbcTemplate.query(sql,new StudentRowMapper());
        }
        return null;
    }

    public List<Student> getNoElectionStudents(int term, int section, int time, int week) {
        String scheduleQuery = "SELECT COUNT(*) FROM COURSE_SCHEDULING " +
                "WHERE RESTRICTION_TERM&" + term + "=" + term +
                " AND RESTRICTION_WEEK&" + week + "=" + week +
                " AND RESTRICTION_TIME&" + time + "=" + time +
                " AND RESTRICTION_SECTION&" + section + "=" + section;
        int scheduleCount=this.jdbcTemplate.queryForInt(scheduleQuery);
        if(scheduleCount!=0){
            String sql="SELECT * FROM STUDENT " +
                    "WHERE ID NOT IN (SELECT DISTINCT A.STUDENT_ID "  +
                    "FROM STUDENT_COURSE_ELECTIVE A "  +
                    "WHERE A.ELECTIVE_TERM=" + term  +
                    " AND A.ELECTIVE_SECTION=" + section +
                    " AND A.ELECTIVE_TIME=" + time  +
                    " AND A.ELECTIVE_WEEK=" + week +
                    " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "')";
            return this.jdbcTemplate.query(sql,new StudentRowMapper());
        }
        return null;
    }

    public List<StudyLogSituation> getStudyLogSituation(int scheduleId, int term, int section, int time, int week) {
        String sql = "SELECT DISTINCT A.LOG_DATE," +
                //出勤的学生数
                "(SELECT COUNT(B.STUDY_LOG_ID) FROM STUDY_LOGS B " +
                "LEFT JOIN STUDENT_COURSE_ELECTIVE C ON B.STUDENT_COURSE_ELECTIVE_ID=C.ID " +
                "WHERE C.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND C.ELECTIVE_TERM=" + term +
                " AND C.ELECTIVE_SECTION=" + section +
                " AND C.ELECTIVE_TIME=" + time +
                " AND C.ELECTIVE_WEEK=" + week +
                " AND YEAR(C.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "' " +
                " AND B.LOG_DATE=A.LOG_DATE " +
                " AND B.LOG_TYPE=1" +
                " AND B.LOG_VALUE=1" +
                " ) AS ATTENDANCE_YES," +
                //未出勤的学生数
                "(SELECT COUNT(B.STUDY_LOG_ID) FROM STUDY_LOGS B " +
                "LEFT JOIN STUDENT_COURSE_ELECTIVE C ON B.STUDENT_COURSE_ELECTIVE_ID=C.ID " +
                "WHERE C.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND C.ELECTIVE_TERM=" + term +
                " AND C.ELECTIVE_SECTION=" + section +
                " AND C.ELECTIVE_TIME=" + time +
                " AND C.ELECTIVE_WEEK=" + week +
                " AND YEAR(C.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "' " +
                " AND B.LOG_DATE=A.LOG_DATE " +
                " AND B.LOG_TYPE=1" +
                " AND B.LOG_VALUE=0" +
                " ) AS ATTENDANCE_NO," +
                //参与考勤的学生总数
                "(SELECT COUNT(B.STUDY_LOG_ID) FROM STUDY_LOGS B " +
                "LEFT JOIN STUDENT_COURSE_ELECTIVE C ON B.STUDENT_COURSE_ELECTIVE_ID=C.ID " +
                "WHERE C.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND C.ELECTIVE_TERM=" + term +
                " AND C.ELECTIVE_SECTION=" + section +
                " AND C.ELECTIVE_TIME=" + time +
                " AND C.ELECTIVE_WEEK=" + week +
                " AND YEAR(C.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "' " +
                " AND B.LOG_DATE=A.LOG_DATE " +
                " AND B.LOG_TYPE=1" +
                " ) AS ATTENDANCE_ALL" +
                " FROM STUDY_LOGS A " +
                "LEFT JOIN STUDENT_COURSE_ELECTIVE B ON A.STUDENT_COURSE_ELECTIVE_ID=B.ID " +
                "WHERE B.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND B.ELECTIVE_TERM=" + term +
                " AND B.ELECTIVE_SECTION=" + section +
                " AND B.ELECTIVE_TIME=" + time +
                " AND B.ELECTIVE_WEEK=" + week +
                " AND YEAR(B.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'" +
                " AND A.LOG_TYPE=1";
        return this.jdbcTemplate.query(sql, new StudyLogSituationRowMapper());
    }

    public List<TeacherCourseElective> getTeacherCourseTable(int teacherId, int term, int section) {
        String sql = "SELECT DISTINCT A.COURSE_SCHEDULING_ID,C.COURSE_NAME,E.CLASSROOM_LOCATION,A.ELECTIVE_TIME,A.ELECTIVE_WEEK" +
                " FROM STUDENT_COURSE_ELECTIVE A" +
                " LEFT JOIN COURSE_SCHEDULING B ON A.COURSE_SCHEDULING_ID=B.COURSE_SCHEDULING_ID" +
                " LEFT JOIN COURSE C ON B.COURSE_ID=C.ID" +
                " LEFT JOIN CLASSROOM E ON B.CLASSROOM_ID=E.ID" +
                " WHERE B.TEACHER_ID=" + teacherId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'" +
                " ORDER BY B.COURSE_SCHEDULING_ID";
        return this.jdbcTemplate.query(sql, new TeacherCourseTableRowMapper());
    }

    public List<Student> getTeacherCourseStudentList(int scheduleId, int term, int section, int time, int week) {
        String sql = "SELECT * FROM STUDENT " +
                "WHERE ID IN (SELECT A.STUDENT_ID " +
                "FROM STUDENT_COURSE_ELECTIVE A " +
                "WHERE A.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND A.ELECTIVE_TIME=" + time +
                " AND A.ELECTIVE_WEEK=" + week +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "')";
        return this.jdbcTemplate.query(sql, new StudentRowMapper());
    }

    public List<String> getCourseLogsDate(int scheduleId, int term, int section, int time, int week, int logType) {
        String sql = "SELECT DISTINCT LOG_DATE FROM STUDY_LOGS " +
                "WHERE STUDENT_COURSE_ELECTIVE_ID IN (SELECT A.ID " +
                "FROM STUDENT_COURSE_ELECTIVE A " +
                "WHERE A.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND A.ELECTIVE_TIME=" + time +
                " AND A.ELECTIVE_WEEK=" + week +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "')" +
                " AND LOG_TYPE=" + logType +
                " ORDER BY LOG_DATE";
        return this.jdbcTemplate.queryForList(sql, String.class);
    }

    public List<CourseStudentsLog> getCourseStudentsLog(int scheduleId, int term, int section, int time, int week, int logType, String logDate) {
        String sql = "SELECT A.STUDY_LOG_ID,A.LOG_VALUE,A.LOG_CONTENT,C.STUDENT_NAME FROM STUDY_LOGS A " +
                "LEFT JOIN STUDENT_COURSE_ELECTIVE B ON A.STUDENT_COURSE_ELECTIVE_ID=B.ID " +
                "LEFT JOIN STUDENT C ON B.STUDENT_ID=C.ID " +
                "WHERE B.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND B.ELECTIVE_TERM=" + term +
                " AND B.ELECTIVE_SECTION=" + section +
                " AND B.ELECTIVE_TIME=" + time +
                " AND B.ELECTIVE_WEEK=" + week +
                " AND YEAR(B.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'" +
                " AND A.LOG_TYPE=" + logType +
                " AND A.LOG_DATE='" + logDate + "'";
        return this.jdbcTemplate.query(sql, new CourseStudentsLogRowMapper());
    }

    public int updateStudentCourseLogs(CourseStudentsLog courseStudentsLog) {

        String sql = "UPDATE STUDY_LOGS SET LOG_VALUE=" + courseStudentsLog.getLogValue() +
                ",LOG_CONTENT='" + courseStudentsLog.getLogContent() +
                "' WHERE STUDY_LOG_ID=" + courseStudentsLog.getLogId();
        return this.jdbcTemplate.update(sql);
    }

    public int deleteCourseLogs(int scheduleId, int term, int section, int time, int week, int logType, Date logDate) {
        String sql = "DELETE FROM STUDY_LOGS WHERE STUDENT_COURSE_ELECTIVE_ID=? AND LOG_DATE=? AND LOG_TYPE=?";
        String querySql = "SELECT A.ID " +
                "FROM STUDENT_COURSE_ELECTIVE A " +
                "WHERE A.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND A.ELECTIVE_TIME=" + time +
                " AND A.ELECTIVE_WEEK=" + week +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        List<Integer> studentCourseElectiveIds = this.jdbcTemplate.queryForList(querySql, Integer.class);
        List<Object[]> params = new ArrayList<Object[]>();
        for (Integer studentCourseElectiveId : studentCourseElectiveIds) {
            Object[] newRow = new Object[]{studentCourseElectiveId.intValue(), logDate, logType};
            params.add(newRow);
        }
        int[] counts = this.jdbcTemplate.batchUpdate(sql, params);
        int countSum = 0;
        for (int count : counts) {
            countSum += count;
        }
        return countSum;
    }

    public int getCourseStudentIsObtainCredit(int studentId, int scheduleId, int term, int section, int time, int week) {
        String sql = "SELECT A.IS_OBTAIN_CREDITS " +
                "FROM STUDENT_COURSE_ELECTIVE A " +
                "WHERE A.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND A.ELECTIVE_TIME=" + time +
                " AND A.ELECTIVE_WEEK=" + week +
                " AND A.STUDENT_ID=" + studentId +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        return this.jdbcTemplate.queryForInt(sql);
    }

    public int updateCourseStudentIsObtainCredit(int studentId, int scheduleId, int term, int section, int time, int week, int isObtainCredits) {
        String sql = "UPDATE STUDENT_COURSE_ELECTIVE SET IS_OBTAIN_CREDITS=" + isObtainCredits +
                " WHERE COURSE_SCHEDULING_ID=" + scheduleId +
                " AND ELECTIVE_TERM=" + term +
                " AND ELECTIVE_SECTION=" + section +
                " AND ELECTIVE_TIME=" + time +
                " AND ELECTIVE_WEEK=" + week +
                " AND STUDENT_ID=" + studentId +
                " AND YEAR(ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        return this.jdbcTemplate.update(sql);
    }

    public int getCourseStudentLogCountByType(int studentId, int scheduleId, int term, int section, int time, int week, int logType, int logValue) {
        String sql = "SELECT COUNT(*) FROM STUDY_LOGS WHERE STUDENT_COURSE_ELECTIVE_ID IN (" +
                "SELECT A.ID " +
                "FROM STUDENT_COURSE_ELECTIVE A " +
                "WHERE A.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND A.ELECTIVE_TIME=" + time +
                " AND A.ELECTIVE_WEEK=" + week +
                " AND A.STUDENT_ID=" + studentId +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'" +
                ") AND LOG_TYPE=" + logType;
        if (logValue != -1)
            sql += " AND LOG_VALUE=" + logValue;
        return this.jdbcTemplate.queryForInt(sql);

    }

    public int addCourseLogs(int scheduleId, int term, int section, int time, int week, int logType, Date logDate) {
        String sql = "INSERT INTO STUDY_LOGS (LOG_TYPE,STUDENT_COURSE_ELECTIVE_ID,LOG_DATE,LOG_VALUE) " +
                "VALUES (?,?,?,?)";
        String querySql = "SELECT A.ID " +
                "FROM STUDENT_COURSE_ELECTIVE A " +
                "WHERE A.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND A.ELECTIVE_TERM=" + term +
                " AND A.ELECTIVE_SECTION=" + section +
                " AND A.ELECTIVE_TIME=" + time +
                " AND A.ELECTIVE_WEEK=" + week +
                " AND YEAR(A.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'";
        List<Integer> studentCourseElectiveIds = this.jdbcTemplate.queryForList(querySql, Integer.class);
        List<Object[]> params = new ArrayList<Object[]>();
        for (Integer studentCourseElectiveId : studentCourseElectiveIds) {
            Object[] newRow = new Object[]{logType, studentCourseElectiveId.intValue(), logDate, true};
            params.add(newRow);
        }
        int[] counts = this.jdbcTemplate.batchUpdate(sql, params);
        int countSum = 0;
        for (int count : counts) {
            countSum += count;
        }
        return countSum;
    }

    public List<CourseStudentLogs> getCourseStudentLogs(int studentId, int scheduleId, int term, int section, int time, int week, int logType) {
        String sql = "SELECT A.LOG_DATE,A.LOG_VALUE,A.LOG_CONTENT FROM STUDY_LOGS A " +
                "INNER JOIN STUDENT_COURSE_ELECTIVE B ON A.STUDENT_COURSE_ELECTIVE_ID=B.ID " +
                " AND B.STUDENT_ID=" + studentId +
                " AND B.COURSE_SCHEDULING_ID=" + scheduleId +
                " AND B.ELECTIVE_TERM=" + term +
                " AND B.ELECTIVE_SECTION=" + section +
                " AND B.ELECTIVE_TIME=" + time +
                " AND B.ELECTIVE_WEEK=" + week +
                " AND YEAR(B.ELECTIVE_DATE)='" + Calendar.getInstance().get(Calendar.YEAR) + "'" +
                " AND A.LOG_TYPE=" + logType +
                " ORDER BY A.LOG_DATE ASC";
        return this.jdbcTemplate.query(sql, new CourseStudentLogsRowMapper());
    }

    private class CourseStudentLogsRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            CourseStudentLogs courseStudentLogs = new CourseStudentLogs();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            courseStudentLogs.setLogDate(sdf.format(resultSet.getDate("LOG_DATE")));
            courseStudentLogs.setLogValue(resultSet.getInt("LOG_VALUE"));
            courseStudentLogs.setLogContent(resultSet.getString("LOG_CONTENT"));
            return courseStudentLogs;
        }
    }

    private class CourseStudentsLogRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            CourseStudentsLog courseStudentsLog = new CourseStudentsLog();
            courseStudentsLog.setLogId(resultSet.getInt("STUDY_LOG_ID"));
            courseStudentsLog.setStudentName(resultSet.getString("STUDENT_NAME"));
            courseStudentsLog.setLogValue(resultSet.getBoolean("LOG_VALUE") ? 1 : 0);
            courseStudentsLog.setLogContent(resultSet.getString("LOG_CONTENT"));
            return courseStudentsLog;
        }
    }

    private class StudyLogSituationRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            StudyLogSituation studyLogSituation = new StudyLogSituation();
            studyLogSituation.setLogDate(resultSet.getString("LOG_DATE"));
            studyLogSituation.setAttendanceYes(resultSet.getInt("ATTENDANCE_YES"));
            studyLogSituation.setAttendanceNo(resultSet.getInt("ATTENDANCE_NO"));
            studyLogSituation.setAttendanceAll(resultSet.getInt("ATTENDANCE_ALL"));
            return studyLogSituation;
        }
    }

    private class StudentRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setId(resultSet.getInt("ID"));
            student.setBirthday(resultSet.getString("STUDENT_BIRTH"));
            student.setName(resultSet.getString("STUDENT_NAME"));
            student.setNation(resultSet.getInt("STUDENT_RACE"));
            student.setSex(resultSet.getInt("STUDENT_SEX"));
            student.setIdentityCardNumber(resultSet.getString("STUDENT_ID_NO"));
            student.setStudentNumber(resultSet.getString("STUDENT_NO"));
            student.setExecutiveClass(resultSet.getInt("ADMIN_CLASS_ID"));
            student.setTeachingClass(resultSet.getInt("STUDY_CLASS_ID"));
            return student;
        }
    }

    private class TeacherCourseTableRowMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            TeacherCourseElective teacherCourseElective = new TeacherCourseElective();
            teacherCourseElective.setScheduleId(resultSet.getInt("COURSE_SCHEDULING_ID"));
            teacherCourseElective.setCourseName(resultSet.getString("COURSE_NAME"));
            teacherCourseElective.setClassroomLocation(resultSet.getString("CLASSROOM_LOCATION"));
            teacherCourseElective.setElectiveTime(resultSet.getInt("ELECTIVE_TIME"));
            teacherCourseElective.setElectiveWeek(resultSet.getInt("ELECTIVE_WEEK"));
            return teacherCourseElective;
        }
    }
}
